using System.Data.SqlClient;
using NUnit.Framework;
using TDUnit.MsSql;

namespace TDUnit.Tests
{
    [TestFixture]
    [Category("Requires Sql Server or Sql Express")]
    public class SqlServerScriptExecutionEngineTests
    {
        private const string connectionString =
            @"Server=.\SQLExpress;Database=TDUnitScriptExecutionTests; Trusted_Connection=Yes;";
        private SqlServerScriptExecutionEngine engine;

        #region SetUp/TearDown
        [SetUp]
        public void TestFixtureSetUp()
        {
            engine = new SqlServerScriptExecutionEngine(connectionString);
        }

        [TearDown]
        public void TearDown()
        {
            engine.Dispose();
        }

        #endregion

        [Test]
        public void ExecuteNonQueryScript()
        {
            try
            {
                engine.ExecuteNonQueryScript(@"INSERT INTO SimpleTable (TestColumn) VALUES ('TestValue');");
                Assert.AreEqual(1, int.Parse(ExecuteScalarQuery(@"SELECT COUNT(*) FROM SimpleTable;").ToString()));
            }
            finally
            {
                ExecuteNonQuery(@"DELETE FROM SimpleTable;");
            }
        }

        [Test]
        public void ExecuteQueryScript()
        {
            try
            {
                ExecuteNonQuery(@"INSERT INTO SimpleTable (TestColumn) VALUES ('TestValue');");
                var data = engine.ExecuteScalarQueryScript(@"SELECT COUNT(*) FROM SimpleTable;");
                Assert.AreEqual(1, int.Parse(data.ToString()));
            }
            finally
            {
                ExecuteNonQuery(@"DELETE FROM SimpleTable;");
            }
        }

        [Test]
        public void ExecuteQueryShouldReturnDataSetWithQueryResults()
        {
            try
            {
                ExecuteNonQuery("INSERT INTO SimpleTable (TestColumn) VALUES ('TestValue')");
                var ds = engine.ExecuteQuery("SELECT * FROM SimpleTable", "SimpleTableResults", "SimpleTable");
                Assert.IsNotNull(ds.Tables["SimpleTable"]);
                Assert.IsNotNull(ds.Tables["SimpleTable"].Columns["TestColumn"]);
                Assert.AreEqual("TestValue", ds.Tables["SimpleTable"].Rows[0]["TestColumn"].ToString().Trim());
            }
            finally
            {
                ExecuteNonQuery("DELETE FROM SimpleTable");
            }

        }

        #region Helper Methods
        private static object ExecuteScalarQuery(string query)
        {
            object data;
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var cmd = new SqlCommand(query, connection))
                {
                    data = cmd.ExecuteScalar();
                }
            }
            return data;
        }

        private static void ExecuteNonQuery(string statement)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var cmd = new SqlCommand(statement, connection))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
        #endregion
    }
}